/**
 * @(#)TaskManagerSQLiteDatabase.java        1.0 2/26/12
 * 
 * Copyright (c) 2012 public static droid main
 * Georgia Institute of Technology, Atlanta, GA
 * All rights reserved.
 */

package edu.gatech.oad.publicstaticdroidmain.taskmanager.persistence;

import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import edu.gatech.oad.publicstaticdroidmain.taskmanager.model.Task;

/**
 * An abstraction of an Android SQLite database used to store and
 * manipulate user data.  The database has one default user that
 * is always present (username: default, password: default).
 * 
 * @version 1.0 2/26/12
 * @author Daniel Hull
 *
 */
public class TaskManagerSQLiteDatabase implements DataSourceInterface {

    private static final String USER_TABLE = "Users";
    private static final String TASK_TABLE = "Tasks";
    private static final String CATEGORY_TABLE = "Categories";

    private static final String USER_T_NAME = "Name";
    private static final String USER_T_EMAIL = "Email";
    private static final String USER_T_USERNAME = "Username";
    private static final String USER_T_PASSWORD = "Password";

    private static final String TASK_T_ID = "ID";
    private static final String TASK_T_USERNAME = "Username";
    private static final String TASK_T_NAME = "TName";
    private static final String TASK_T_DESCRIPTION = "TDescription";
    private static final String TASK_T_DUE_DATE = "TdueDate";
    private static final String TASK_T_LATITUDE = "Tlat";
    private static final String TASK_T_LONGITUDE = "Tlon";
    private static final String TASK_T_RECURRENCE = "Trecurrence";
    private static final String TASK_T_CATEGORY = "Category";
    private static final String TASK_T_COMPLETE = "TComplete";

    private static final String CATEGORY_T_USERNAME = "Username";
    private static final String CATEGORY_T_CATEGORY = "Category";

    private static final String COUNT = "count(*)";

    private static final String DEFAULT_USER = "default";
    private static final String[] DEFAULT_CATEGORIES = {"Personal", "School", "Work"};

    private static final String SORT_BY = TASK_T_DUE_DATE;
    private static final String SORT_ORDER = "ASC";

    private SQLiteDatabase database;
    private DBHelper helper;
    private final Context context;

    /**
     * Constructs a new Database that allows access to the TaskManager's
     * tables.
     * 
     * @param context the context in which this database will be used
     */
    public TaskManagerSQLiteDatabase(Context context) {
	this.context = context;
    }

    /**
     * Readies this database for use.  You must call this method before
     * attempting to use any of the other methods in this class.
     */
    public void open() {
	helper = new DBHelper(context);
	database = helper.getWritableDatabase();

	if (!userExists(DEFAULT_USER)) {
	    createUser(DEFAULT_USER, DEFAULT_USER, DEFAULT_USER, DEFAULT_USER);
	}
    }

    /**
     * Closes the connection to the underlying Android database.  Call this
     * method when you are finished using this database object.  After calling
     * this method, none of the other methods in this class will work.  If
     * you want to reuse this database object after calling this method, you
     * need to call open() on it again.
     */
    public void close() {
	helper.close();
    }

    /**
     * Returns a String of the form "col1=? col2=? ..." with the specified
     * columns that can be used with Android SQL queries.
     * 
     * @param columns the columns to include in the string
     * @return the generated String
     */
    private String generateAndroidWhereClause(String ... columns) {
	StringBuffer whereClause = new StringBuffer();

	int lastIndex = columns.length - 1;

	for (int i = 0; i <= lastIndex; i++) {
	    whereClause.append(columns[i] + "=?");

	    if (i != lastIndex) {
		whereClause.append(" AND ");
	    }
	}

	return whereClause.toString();
    }

    /**
     * Determines whether a user with the specified username exists in this
     * database.
     * 
     * @param username the username whose existence is to be determined
     * @return true if the specified username exists in this database; false
     * 		otherwise
     */
    public boolean userExists(String username) {
	if (username == null) {
	    throw new IllegalArgumentException("username cannot be null");
	}

	Cursor c = database.query(USER_TABLE, new String[] { COUNT }, 
		generateAndroidWhereClause(USER_T_USERNAME), 
		new String[] {username}, null, null, null);

	c.moveToFirst();

	return (c.getInt(0) > 0);
    }

    /**
     * Determines whether the specified username-password combination corresponds
     * to an actual account in the database.
     * 
     * @param username the username of the account
     * @param password the password of the account
     * @return true if the specified username-password combination is valid;
     * 		false otherwise
     */
    public boolean verifyCredentials(String username, String password) {
	if (username == null) {
	    throw new IllegalArgumentException("username cannot be null");
	}

	if (password == null) {
	    throw new IllegalArgumentException("password cannot be null");
	}

	Cursor c = database.query(USER_TABLE, new String[] {"count(*)"}, 
		generateAndroidWhereClause(USER_T_USERNAME, USER_T_PASSWORD), 
		new String[] {username, password}, null, null, null);

	c.moveToFirst();

	return (c.getInt(0) > 0);
    }

    /**
     * Creates a user in the database with the specified information if
     * an account with the specified username does not already exist.
     * 
     * @param username the username of the account to create
     * @param password the password of the account to create
     * @param realName the real name of the person who owns this account
     * @param emailAddress the email address of the person who owns this account
     * @return true if the account was successfully created; false if an account
     * 		with the specified username already exists
     */
    public boolean createUser(String username, String password, String realName, String emailAddress) {
	if (username == null) {
	    throw new IllegalArgumentException("username cannot be null");
	}

	if (password == null) {
	    throw new IllegalArgumentException("password cannot be null");
	}

	if (realName == null) {
	    throw new IllegalArgumentException("real name cannot be null");
	}

	if (emailAddress == null) {
	    throw new IllegalArgumentException("email address cannot be null");
	}

	boolean createSuccessful = false;

	if (!userExists(username)) {
	    ContentValues values = new ContentValues();
	    values.put(USER_T_NAME, realName);
	    values.put(USER_T_EMAIL, emailAddress);
	    values.put(USER_T_USERNAME, username);
	    values.put(USER_T_PASSWORD, password);

	    database.insert(USER_TABLE, null, values);

	    for (String category : DEFAULT_CATEGORIES) {
		addCategory(username, category);
	    }

	    createSuccessful = true;
	}

	return createSuccessful;
    }

    /**
     * Changes the specified username to the specified new username.
     * 
     * @param username the username to change
     * @param newUsername the new username
     * @return true if the username was successfully updated; false if
     * 		the specifide username does not exist
     */
    public boolean updateUsername(String username, String newUsername) {
	if (username == null) {
	    throw new IllegalArgumentException("username cannot be null");
	}

	if (newUsername == null) {
	    throw new IllegalArgumentException("new username cannot be null");
	}

	boolean updateSuccessful = false;

	if (userExists(username)) {
	    ContentValues userTableValues = new ContentValues();
	    userTableValues.put(USER_T_USERNAME, newUsername);

	    ContentValues taskTableValues = new ContentValues();
	    taskTableValues.put(TASK_T_USERNAME, newUsername);

	    ContentValues categoryTableValues = new ContentValues();
	    categoryTableValues.put(CATEGORY_T_USERNAME, newUsername);

	    String[] selectionArgs = { username };

	    database.update(USER_TABLE, userTableValues, generateAndroidWhereClause(USER_T_USERNAME), selectionArgs);
	    database.update(TASK_TABLE, taskTableValues, generateAndroidWhereClause(TASK_T_USERNAME), selectionArgs);
	    database.update(CATEGORY_TABLE, categoryTableValues, generateAndroidWhereClause(CATEGORY_T_USERNAME), selectionArgs);

	    updateSuccessful = true;
	}

	return updateSuccessful;
    }

    /** 
     * Changes the password of the account with the specified username to
     * the new specified password.
     * 
     * @param username the username of the account to edit
     * @param oldPassword the account's current password
     * @param newPassword the new password
     * @return true if the password was successfully changed; false if the
     * 		specified username does not exist or if the old password
     * 		is incorrect
     */
    public boolean updatePassword(String username, String oldPassword, String newPassword) {
	if (username == null) {
	    throw new IllegalArgumentException("username cannot be null");
	}

	if (oldPassword == null) {
	    throw new IllegalArgumentException("old password cannot be null");
	}

	if (newPassword == null) {
	    throw new IllegalArgumentException("new password cannot be null");
	}

	boolean updateSuccessful = false;

	if (verifyCredentials(username, oldPassword)) {
	    ContentValues values = new ContentValues();
	    values.put(USER_T_PASSWORD, newPassword);

	    database.update(USER_TABLE, values, generateAndroidWhereClause(USER_T_USERNAME), new String[] { username });

	    updateSuccessful = true;
	}

	return updateSuccessful;
    }

    /** 
     * Changes the real name of the account with the specified username to
     * the new specified real name.
     * 
     * @param username the username of the account to edit
     * @param newRealName the new real name
     * @return true if the real name was successfully changed; false if the
     * 		specified username does not exist
     */
    public boolean updateRealName(String username, String newRealName) {
	if (username == null) {
	    throw new IllegalArgumentException("username cannot be null");
	}

	if (newRealName == null) {
	    throw new IllegalArgumentException("new real name cannot be null");
	}

	boolean updateSuccessful = false;

	if (userExists(username)) {
	    ContentValues values = new ContentValues();
	    values.put(USER_T_NAME, newRealName);

	    database.update(USER_TABLE, values, generateAndroidWhereClause(USER_T_USERNAME), new String[] { username });

	    updateSuccessful = true;
	}

	return updateSuccessful;
    }

    /** 
     * Changes the email address of the account with the specified username to
     * the specified new email address.
     * 
     * @param username the username of the account to edit
     * @param newEmailAddress the new email address
     * @return true if the email address was successfully changed; false if the
     * 		specified username does not exist
     */
    public boolean updateEmailAddress(String username, String newEmailAddress) {
	if (username == null) {
	    throw new IllegalArgumentException("username cannot be null");
	}

	if (newEmailAddress == null) {
	    throw new IllegalArgumentException("new email address cannot be null");
	}

	boolean updateSuccessful = false;

	if (userExists(username)) {
	    ContentValues values = new ContentValues();
	    values.put(USER_T_EMAIL, newEmailAddress);

	    database.update(USER_TABLE, values, generateAndroidWhereClause(USER_T_USERNAME), new String[] { username });

	    updateSuccessful = true;
	}

	return updateSuccessful;
    }

    /**
     * Returns the real name of the account with the specified username.
     * 
     * @param username the username of the account
     * @return the real name of the account with the specified username
     */
    public String getRealName(String username) {
	String realName = null;

	if (userExists(username)) {
	    Cursor c = database.query(USER_TABLE, new String[] {USER_T_NAME}, 
		    generateAndroidWhereClause(USER_T_USERNAME), 
		    new String[] {username}, null, null, null);

	    c.moveToFirst();

	    realName = c.getString(0);
	}

	return realName;
    }

    /**
     * Returns the email address of the account with the specified username.
     * 
     * @param username the username of the account
     * @return the email address of the account with the specified username
     */
    public String getEmailAddress(String username) {
	String emailAddress = null;

	if (userExists(username)) {
	    Cursor c = database.query(USER_TABLE, new String[] {USER_T_EMAIL}, 
		    generateAndroidWhereClause(USER_T_USERNAME), 
		    new String[] {username}, null, null, null);

	    c.moveToFirst();

	    emailAddress = c.getString(0);
	}

	return emailAddress;
    }

    /**
     * Adds the specified category to the account with the specified
     * username.
     * 
     * @param username the username of the account
     * @param category the name of the category to add
     * @return true if the category was successfully added; false if
     * 		the category already exists
     */
    public boolean addCategory(String username, String category) {
	if (username == null) {
	    throw new IllegalArgumentException("username cannot be null");
	}

	if (category == null) {
	    throw new IllegalArgumentException("category cannot be null");
	}

	category = category.trim();

	if (category.equals("")) {
	    throw new IllegalArgumentException("category name cannot be blank");
	}

	boolean addSuccessful = false;

	if (userExists(username) && !categoryExists(username, category)) {
	    ContentValues values = new ContentValues();
	    values.put(CATEGORY_T_USERNAME, username);
	    values.put(CATEGORY_T_CATEGORY, category);

	    database.insert(CATEGORY_TABLE, null, values);

	    addSuccessful = true;
	}

	return addSuccessful;
    }

    /**
     * Changes the name of of the specified category to the specified new name.
     * 
     * @param username the username of the account to edit
     * @param oldName the name of the category whose name to change
     * @param newName the new name for the category
     * @return true if the category's name was successfully changed; false if
     * 		the specified category does not exist
     */
    public boolean updateCategoryName(String username, String oldName, String newName) {
	if (username == null) {
	    throw new IllegalArgumentException("username cannot be null");
	}

	if (oldName == null) {
	    throw new IllegalArgumentException("old category name cannot be null");
	}

	if (newName == null) {
	    throw new IllegalArgumentException("new name cannot be null");
	}

	boolean updateSuccessful = false;

	if (categoryExists(username, oldName)) {
	    ContentValues categoryTableValues = new ContentValues();
	    categoryTableValues.put(CATEGORY_T_CATEGORY, newName);
	    
	    ContentValues taskTableValues = new ContentValues();
	    taskTableValues.put(TASK_T_CATEGORY, newName);
	    
	    String[] selectionArgs = new String[] { username, oldName };
	    
	    database.update(CATEGORY_TABLE, categoryTableValues, 
		    generateAndroidWhereClause(CATEGORY_T_USERNAME, CATEGORY_T_CATEGORY), 
		    selectionArgs );
	    
	    database.update(TASK_TABLE, taskTableValues, 
		    generateAndroidWhereClause(TASK_T_USERNAME, TASK_T_CATEGORY), 
		    selectionArgs );
	    
	    updateSuccessful = true;
	}

	return updateSuccessful;
    }

    /**
     * Removes the category with the specified name from the specified
     * user's account and places the tasks in that category into the
     * specified replacement category.
     * 
     * @param username the username of the account
     * @param category the name of the category to remove
     * @param replacement the name of the category in which to place the 
     * 		tasks in the deleted category
     * @return true if the specified category was successfully removed;
     * 		false if the specified category does not exist
     */
    public boolean removeCategory(String username, String category, String replacement) {
	if (username == null) {
	    throw new IllegalArgumentException("username cannot be null");
	}

	if (category == null) {
	    throw new IllegalArgumentException("category cannot be null");
	}

	if (replacement == null) {
	    throw new IllegalArgumentException("replacement category cannot be null");
	}

	boolean deleteSuccessful = false;

	if (userExists(username) && categoryExists(username, category) && categoryExists(username, replacement)) {
	    ContentValues taskTableValues = new ContentValues();
	    taskTableValues.put(TASK_T_CATEGORY, replacement);

	    ContentValues categoryTableValues = new ContentValues();
	    categoryTableValues.put(CATEGORY_T_CATEGORY, replacement);

	    String[] selectionArgs = { category };

	    database.delete(CATEGORY_TABLE, generateAndroidWhereClause(CATEGORY_T_USERNAME, CATEGORY_T_CATEGORY), new String[] { username, category });

	    database.update(TASK_TABLE, taskTableValues, generateAndroidWhereClause(TASK_T_CATEGORY), selectionArgs);
	    database.update(CATEGORY_TABLE, categoryTableValues, generateAndroidWhereClause(CATEGORY_T_CATEGORY), selectionArgs);


	    deleteSuccessful = true;
	}

	return deleteSuccessful;
    }

    /**
     * Determines whether the specified category exists in the specified
     * username's account.
     * 
     * @param username the username of the account
     * @param password the name of the category whose existence to determine
     * @return true if the specified category exists; false otherwise
     */
    public boolean categoryExists(String username, String category) {
	boolean categoryExists = false;

	if (userExists(username)) {
	    Cursor c = database.query(CATEGORY_TABLE, new String[] {COUNT}, 
		    generateAndroidWhereClause(CATEGORY_T_USERNAME, CATEGORY_T_CATEGORY), 
		    new String[] {username, category}, null, null, null);
	    c.moveToFirst();
	    categoryExists = (c.getInt(0) > 0);
	}

	return categoryExists;
    }

    /**
     * Generates an array containing the names of the categories in the
     * specified account.
     * 
     * @param username the username of the account
     * @return an array containing the names of the categories in the
     * 		specified account
     */
    public String[] getCategories(String username) {
	String[] categories = null;

	if (userExists(username)) {
	    Cursor c = database.query(CATEGORY_TABLE, new String[] { CATEGORY_T_CATEGORY }, 
		    generateAndroidWhereClause(CATEGORY_T_USERNAME), 
		    new String[] { username }, null, null, null);

	    int numCategories = c.getCount();
	    categories = new String[numCategories];

	    if (numCategories > 0) {
		int index = 0;
		for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
		    categories[index++] = c.getString(0);
		}
	    }
	}

	return categories;
    }

    /**
     * Generates an array containing the Tasks in the specified category
     * in the specified account.
     * 
     * @param username the username of the account
     * @param category the name of the category whose Tasks to retrieve
     * @return an array containing the Tasks in the specified category
     */
    public Task[] getTasksInCategory(String username, String category) {
	return getTasks(username, generateAndroidWhereClause(TASK_T_CATEGORY), new String[] { category });
    }

    /**
     * Generates an array containing the Tasks on or after the specified
     * date in the specified account.
     * 
     * @param username the username of the account
     * @param date the dividing date
     * @return an array containing the Tasks on or after the specified date
     */
    public Task[] getTasksAfterDate(String username, Date date) {
	String whereClause = String.format("%s >= %d", TASK_T_DUE_DATE, date.getTime());
	return getTasks(username, whereClause, null);
    }

    /**
     * Generates an array containing the Tasks in with the specified completion
     * status in the specified account.
     * 
     * @param username the username of the account
     * @param completionStatus the completion status of the Tasks to retrieve
     * @return an array containing the Tasks with the specified completion status
     */
    public Task[] getTasksWithCompletionStatus(String username, boolean completionStatus) {
	return getTasks(username, generateAndroidWhereClause(TASK_T_COMPLETE), new String[] {"" + (completionStatus?1:0)});
    }

    /**
     * Adds the specified Task to the account with the specified username.
     * 
     * @param username the username of the account
     * @param t the Task to add to the account
     * @return true if the Task was successfully added; false if a Task with
     * 		the ID of the specified Task already exists in the account
     */
    public boolean addTask(String username, Task t) {
	boolean addSuccessful = false;

	if (userExists(username)) {
	    ContentValues values = new ContentValues();
	    values.put(TASK_T_ID, t.getId());
	    values.put(TASK_T_USERNAME, username);
	    values.put(TASK_T_NAME, t.getName());
	    values.put(TASK_T_DESCRIPTION, t.getDescription());
	    values.put(TASK_T_DUE_DATE, t.getDueDate().getTime());
	    values.put(TASK_T_LATITUDE, t.getLatitude());
	    values.put(TASK_T_LONGITUDE, t.getLongitude());
	    values.put(TASK_T_RECURRENCE, t.getRecurrence().toString());
	    values.put(TASK_T_CATEGORY, t.getCategory());
	    values.put(TASK_T_COMPLETE, ((t.isCompleted()==true)?1:0));

	    database.insert(TASK_TABLE, null, values);

	    addSuccessful = true;
	}

	return addSuccessful;
    }

    /**
     * Updates the specified Task's information (except its ID) in the database.
     * 
     * @param username the username of the account
     * @param t the Task to update
     * @param true if the Task was successfully updated; false if the
     * 		Task does not exist in the database
     */
    public boolean updateTask(String username, Task t) {
	//Doesn't update the ID since it's the primary key
	boolean updateSuccessful = false;

	long taskId = t.getId();

	if (userExists(username) && taskExists(username, taskId)) {
	    ContentValues values = new ContentValues();
	    values.put(TASK_T_NAME, t.getName());
	    values.put(TASK_T_DESCRIPTION, t.getDescription());
	    values.put(TASK_T_DUE_DATE, t.getDueDate().getTime());
	    values.put(TASK_T_LATITUDE, t.getLatitude());
	    values.put(TASK_T_LONGITUDE, t.getLongitude());
	    values.put(TASK_T_RECURRENCE, t.getRecurrence().toString());
	    values.put(TASK_T_CATEGORY, t.getCategory());
	    values.put(TASK_T_COMPLETE, ((t.isCompleted()==true)?1:0));

	    database.update(TASK_TABLE, values, generateAndroidWhereClause(TASK_T_USERNAME, TASK_T_ID), 
		    new String[] { username, "" + taskId });
	}

	return updateSuccessful;
    }

    /**
     * Removes the Task with the specified ID from the account.
     * 
     * @param username the username of the account
     * @param id the ID of the Task to remove
     * @return true if the Task was successfully removed; false if a Task
     * 		with the specified ID does not exist
     */
    public boolean removeTask(String username, long id) {
	boolean removeSuccessful = false;

	if (userExists(username) && taskExists(username, id)) {
	    database.delete(TASK_TABLE, generateAndroidWhereClause(TASK_T_USERNAME, TASK_T_ID), 
		    new String[] { username, "" + id });
	    removeSuccessful = true;
	}

	return removeSuccessful;
    }

    /**
     * Generates a Task object containing the information of the Task
     * with the specified ID.
     * 
     * @param the username of the account
     * @param id the ID of the Task whose information to retrieve
     * @param a Task object containing the specified Task's information;
     * 		null if a Task with the specified ID does not exist
     */
    public Task getTask(String username, long id) {
	Task t = null;

	Task[] tasks = getTasks(username, generateAndroidWhereClause(TASK_T_ID), new String[] { "" + id });
	if (tasks.length > 0) {
	    t = tasks[0];
	}

	return t;
    }

    /**
     * Returns an array containing all of the Tasks in the specified account.
     * 
     * @param username the username of the account
     * @return an array containing all of the Tasks in the specified account
     */
    public Task[] getAllTasks(String username) {
	return getTasks(username, null, null);
    }

    /**
     * Returns an array of Tasks in the specified account.  Only Tasks that
     * conform to the specified where clause are included.  If a null where
     * clause is specified, all Tasks in the specified account are included.
     * The where clause does not need to include the WHERE statement itself.
     * 
     * @param username the username of the account
     * @param whereClause the where clause specifying what Tasks should be included.
     * 		Does not need to include the actual WHERE statement itself.
     * @return an array containing Tasks in the specified account that conform
     * 		to the specified where clause; all Tasks in the account are returned
     * 		if the where clause is null
     */
    private Task[] getTasks(String username, String whereClause, String[] selectionArgs) {
	Task[] tasks = null;

	if (userExists(username)) {

	    int TNAME=0, TDESCRIPTION=1, TDUEDATE=2, TLAT=3, TLON=4, TRECURRENCE=5, ID=6, CATEGORY=7, TCOMPLETE=8;

	    String[] columns = {TASK_T_NAME, TASK_T_DESCRIPTION, TASK_T_DUE_DATE, TASK_T_LATITUDE, TASK_T_LONGITUDE, 
		    TASK_T_RECURRENCE, TASK_T_ID, TASK_T_CATEGORY, TASK_T_COMPLETE};
	    String orderByClause = SORT_BY + " " + SORT_ORDER;

	    Cursor c2 = database.query(TASK_TABLE, columns, whereClause, selectionArgs, null, null, orderByClause);

	    c2.moveToFirst();
	    int numTasks = c2.getCount();
	    tasks = new Task[numTasks];

	    if (numTasks > 0) {	
		int index = 0;

		for(c2.moveToFirst(); !c2.isAfterLast(); c2.moveToNext()) {
		    Task t = new Task();
		    t.setName(c2.getString(TNAME));
		    t.setDescription(c2.getString(TDESCRIPTION));
		    t.setDueDate(new Date(c2.getLong(TDUEDATE)));
		    t.setLatitude(c2.getDouble(TLAT));
		    t.setLongitude(c2.getDouble(TLON));
		    t.setRecurrence(Task.Recurrence.valueOf(c2.getString(TRECURRENCE)));
		    t.setId(c2.getLong(ID));
		    t.setCategory(c2.getString(CATEGORY));
		    t.setCompleted((c2.getInt(TCOMPLETE)==1)?true:false);

		    tasks[index++] = t;
		}

	    }

	}

	return tasks;
    }

    public void clear() {
	database.execSQL("DELETE FROM " + CATEGORY_TABLE + " WHERE 1");
	database.execSQL("DELETE FROM " + USER_TABLE + " WHERE 1");
	database.execSQL("DELETE FROM " + TASK_TABLE + " WHERE 1");
    }

    /**
     * Determines whether a Task with the specified ID exists in the
     * specified account.
     * 
     * @param username the username of the account
     * @param id the ID of the Task whose existence is to be determined
     * @return true if a Task with the specified ID exists; false otherwise
     */
    private boolean taskExists(String username, long id) {
	if (username == null) {
	    throw new IllegalArgumentException("username cannot be null");
	}

	Cursor c = database.query(TASK_TABLE, new String[] {COUNT}, 
		generateAndroidWhereClause(TASK_T_USERNAME, TASK_T_ID), 
		new String[] {username, "" + id}, null, null, null);

	c.moveToFirst();

	return (c.getInt(0) > 0);
    }

    private static final String DATABASE_NAME = "TaskManager";
    private static final int DATABASE_VERSION = 1;

    /**
     * The helper object that communicates with the underling Android database.
     * 
     * @author Daniel Hull
     */
    private class DBHelper extends SQLiteOpenHelper {

	public DBHelper(Context c) {
	    super(c, DATABASE_NAME, null, DATABASE_VERSION);
	}

	/**
	 * Creates the tables.
	 * 
	 * @param db the database in which to create the tables
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
	    db.execSQL("CREATE TABLE " + USER_TABLE + "(" + 
		    USER_T_NAME + " TEXT NOT NULL, " +
		    USER_T_EMAIL + " TEXT NOT NULL, " +
		    USER_T_USERNAME + " TEXT NOT NULL PRIMARY KEY, " +
		    USER_T_PASSWORD + " TEXT NOT NULL" +
		    ")");
	    db.execSQL("CREATE TABLE " + TASK_TABLE + "(" +
		    TASK_T_ID + " LONG PRIMARY KEY, " +
		    TASK_T_USERNAME + " TEXT NOT NULL, " +
		    TASK_T_NAME + " TEXT NOT NULL, " +
		    TASK_T_DESCRIPTION + " TEXT NOT NULL, " +
		    TASK_T_DUE_DATE + " LONG, " +
		    TASK_T_LATITUDE + " DOUBLE PRECISION, " +
		    TASK_T_LONGITUDE + " DOUBLE PRECISION, " +
		    TASK_T_RECURRENCE + " TEXT, " + 
		    TASK_T_CATEGORY + " TEXT," +
		    TASK_T_COMPLETE + " INTEGER" +
		    ")");
	    db.execSQL("CREATE TABLE " + CATEGORY_TABLE +  "(" +
		    CATEGORY_T_USERNAME + " TEXT NOT NULL, " +
		    CATEGORY_T_CATEGORY + " TEXT NOT NULL" +
		    ")");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	    db.execSQL("DROP TABLE IF EXISTS " + USER_TABLE);
	}


    }

}
